package com.auxgroup.smarthome.utils.excel.util;

import com.auxgroup.smarthome.utils.excel.model.TableEntity;
import com.auxgroup.smarthome.utils.excel.model.DataEntity;
import com.auxgroup.smarthome.utils.excel.model.FieldEntity;
import com.auxgroup.smarthome.utils.excel.reader.ExcelReaderConfig;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 把Excel数据格式为Header标题头和Body内容转换成java实体工具
 * 
 * @className ExcelTableUtil
 * @author laiqiuhua
 * @description
 * @date 2016年2月29日 下午4:05:54
 * @version V1.0
 *
 */
public class ExcelTableUtil {
	private ExcelTableUtil() {}

	/**
	 * 读取Excel表格表头的内容
	 * 
	 * @return String[] 表头内容的数组
	 */
	private static String[] readExcelTitle(Sheet sheet) {
		Row row = sheet.getRow(0);
		if (row == null)
			return null;
		// 标题总列数
		int colNum = row.getLastCellNum();

		String[] title = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			title[i] = getCellFormatValue(row.getCell(i));
		}
		return title;
	}
	
	/**
	 * 返回工作表sheet的一行数据
	 * 
	 * @param sheet
	 *            工作表
	 * @param row
	 *            行
	 * @return
	 */
	public static List<FieldEntity> getLine(Sheet sheet, ExcelReaderConfig excelReaderConfig, int row) {
		// 根据行数取得sheet的一行
		Row rowline = sheet.getRow(row);
		if (rowline == null)
			return null;

		// 获到当前行的列数
		int filledColumns = rowline.getLastCellNum();
		Cell cell = null;
		// 开始读取的列，从第几列开始读。
		int colStart =  excelReaderConfig.getColStartPosittion();
		List<FieldEntity> list = new ArrayList<FieldEntity>();
		// 遍历所有列
		for (int i = colStart; i < filledColumns; i++) {
			// 取得当前单元格
			cell = rowline.getCell(i);
			String column =  excelReaderConfig.getColumns()[i - colStart];
			String cellValue = getCellFormatValue(cell);
			FieldEntity fieldEntity = new FieldEntity();
			fieldEntity.setName(column);
			fieldEntity.setValue(cellValue);
			fieldEntity.setIsKey(i == colStart ? FieldEntity.IS_KEY
					: FieldEntity.NOT_KEY);
			list.add(fieldEntity);
		}
		// 返回该行的数据
		return list;
	}
	
	private static FieldEntity getFieldEntityKey(List<FieldEntity> fieldEntityList) {
		for (FieldEntity fieldEntity : fieldEntityList) {
			if (fieldEntity.getIsKey().shortValue() == FieldEntity.IS_KEY
					.shortValue())
				return fieldEntity;
		}
		return null;
	}
	
	/**
	 * 读取当前sheet的数据
	 * @param sheet
	 * @param excelReaderConfig
	 * @return
	 */
	public static List<DataEntity> readSheet(Sheet sheet, ExcelReaderConfig excelReaderConfig) {
		// 根据currSheet值获得当前的工作表Sheet
		int lastRowNum = sheet.getLastRowNum();
		// 判断当前行是否到当前工作表sheet的结尾
		List<DataEntity> dataEntityList = new ArrayList<DataEntity>();
		for (int i = 1; i <= lastRowNum; i++) {
			DataEntity dataEntity = new DataEntity();

			List<FieldEntity> fieldEntityList = getLine(sheet, excelReaderConfig, i);
			if (fieldEntityList != null && !fieldEntityList.isEmpty()) {
				FieldEntity fieldEntity = getFieldEntityKey(fieldEntityList);
				dataEntity.setPkName(fieldEntity.getName());
				dataEntity.setPkVal(fieldEntity.getValue());
				dataEntity.setFieldEntityList(fieldEntityList);

			}
			dataEntityList.add(dataEntity);
		}
		return dataEntityList;
	}
	
	/**
	 * 获取当前表
	 * @param sheet
	 * @param excelReaderConfig
	 * @param isMain
	 * @return
	 */
	public static TableEntity getTableEntity(Sheet sheet, ExcelReaderConfig excelReaderConfig, Short isMain) {
		String[] columns =  readExcelTitle(sheet);
		if (columns == null)
			return null;
		excelReaderConfig.setColumns(columns);
		// 读取当前字段
		List<DataEntity> dataEntityList =  readSheet(sheet, excelReaderConfig);
		TableEntity tableEntity = new TableEntity();
		tableEntity.setTitle(columns);
		tableEntity.setName(sheet.getSheetName());
		tableEntity.setIsMain(isMain);
		tableEntity.setDataEntityList(dataEntityList);
		return tableEntity;
	}


	/**
	 * 取得当前单元格的值
	 * @param cell
	 * @return
	 */
	public static String getCellFormatValue(Cell cell) {
		if (cell == null)
			
			return "";
		String cellvalue = "";
		// 判断当前单元格的type
		switch (cell.getCellTypeEnum()) {
		case STRING:
			// /取得当前Cell的字符串
			cellvalue = cell.getRichStringCellValue().getString();
			break;

		// 如果当前Cell的type为NUMERIC或者_FORMULA
		case NUMERIC:
			cellvalue = String.valueOf(((Double)cell.getNumericCellValue()).intValue());
			break;
		case FORMULA:
			// 判断当前的Cell是否为Date
			if (DateUtil.isCellDateFormatted(cell)) {
				// 如果是在Date类型，则取得该Cell的Date值
				Date date = cell.getDateCellValue();
				cellvalue = DateFormatUtil.format(date);
			} else {
				// 如果是纯数字
				// 取得当前cell的数值
				cellvalue = String.valueOf(cell.getNumericCellValue());
			}
			break;
		case BOOLEAN:
			cellvalue = String.valueOf(cell.getBooleanCellValue());
			break;
		default:
			cellvalue = "";
		}
		return cellvalue;
	}
	
}
